﻿Public Class ReportsForm

    Dim PrintIt As Boolean

    Private Sub ReportsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        HidePanels()
        TheTreeView.Nodes(0).Expand()

        Loadboxes()


    End Sub

    Public Sub LoadCustomReports()
        Dim TheNode As TreeNode
        Dim ArcNode As TreeNode
        Dim CurNode As TreeNode
        Dim sql As String
        Dim TheView As DataView

        sql = "select Report_ID, Report_Name from RD_Published_Reports order by Report_Name"
        TheView = LoadDataView(sql)
        TheNode = TheTreeView.Nodes("Custom_Reports")
        TheNode.Nodes.Clear()
        TheNode.Nodes.Add("Archived_Reports", "Archived Reports", 0, 0)
        TheNode.Nodes.Add("Current_Reports", "Current Reports", 0, 0)
        ArcNode = TheNode.Nodes("Archived_Reports")
        CurNode = TheNode.Nodes("Current_Reports")

        If TheView.Count <> 0 Then

            For Each r In TheView
                If r.Item("Report_name").ToString.ToUpper.Contains("ARCHIVE") Then
                    ArcNode.Nodes.Add(r.Item("Report_ID").ToString, r.Item("Report_name"), 2, 2)
                Else
                    CurNode.Nodes.Add(r.Item("Report_ID").ToString, r.Item("Report_name"), 2, 2)
                End If

            Next
        End If
    End Sub

    Private Sub Loadboxes()
        Dim sql As String

        'Shadow Reports
        ShadowReportBox.SelectedIndex = 0

        'PeriodBox
        Job("Loading Periods...")
        sql = "select * from Periods"
        PeriodBox.DataSource = LoadDataView(sql)
        PeriodBox.DisplayMember = "Period_Name"
        PeriodBox.ValueMember = "Period_ID"
        PeriodBox.SelectedValue = CurrentPeriodID

        'RegionBox
        Job("Loading Regions...")
        sql = "Select '<All>' as Region_Code, '<All>' as Region_Name from Global_Settings_Upgrade UNION ALL " & _
              "Select * from Regions order by Region_Name"
        RegionBox.DataSource = LoadDataView(sql)
        RegionBox.DisplayMember = "Region_Name"
        RegionBox.ValueMember = "Region_Code"
        RegionBox.Refresh()

        'LABox
        LoadLABox()

        'FilterBox
        sql = "Select 'All' as Provider_Type_Code, 'All Providers' Provider_Type_Description " & _
            "union all Select 'Schoo' as Provider_Type_Code, 'School Sixth Forms' as Provider_Type_Description " & _
            "union all Select 'Acade' as Provider_Type_Code, 'Academies' as Provider_Type_Description " & _
            "union all Select 'Other' as Provider_Type_Code, 'All Other' as Provider_Type_Description "

        FilterBox.DataSource = LoadDataView(sql)
        FilterBox.ValueMember = "Provider_Type_Code"
        FilterBox.DisplayMember = "Provider_Type_Description"

        'ProviderBox
        LoadProvidersBox()
        LoadNationalProvidersBox()

        Job("Ready")
    End Sub

    Private Sub LoadNationalProvidersBox()
        Dim sql As String
        Job("Loading National Providers...")
        sql = "select distinct UPIN, Provider_Name from all_provider_fields " & _
            "where La_code = 'NLC' " & _
            "order by Provider_Name "

        NationalProviderBox.DataSource = LoadDataView(sql)
        NationalProviderBox.DisplayMember = "Provider_Name"
        NationalProviderBox.ValueMember = "UPIN"
        Job("Ready")
    End Sub


    Private Sub LoadProvidersBox()
        Dim sql As String
        Job("Loading Providers...")

        Dim filtersql As String
        filtersql = ""

        Try
            If FilterBox.SelectedValue <> "All" Then
                If FilterBox.SelectedValue = "Other" Then
                    filtersql = " and p.Provider_Type_Code not in ('Schoo','Acade')"
                Else
                    filtersql = " and p.Provider_Type_Code = '" & FilterBox.SelectedValue & "'"
                End If
            Else
                filtersql = ""
            End If
        Catch ex As Exception
            'not ready yet
        End Try
        

        sql = "select 0 as UPIN, '<All>' as 'Provider Name' From Global_Settings_Upgrade UNION ALL " & _
                    "select distinct p.UPIN, p.Provider_Name as 'Provider Name' " & _
                    "from Local_Authorities la, Providers p, Relationships rel " & _
                    "where la.LA_Code = rel.LA_Code " & _
                    "and p.UPIN = rel.UPIN " & filtersql & " "

        If LABox.SelectedValue.ToString <> "<All>" Then
            sql = sql & "and la.LA_Code = '" & LABox.SelectedValue.ToString & "'"
        Else
            If RegionBox.SelectedValue.ToString <> "<All>" Then
                sql = sql & " and la.Region_Code = '" & RegionBox.SelectedValue.ToString & "'"

            End If
        End If

        sql = sql & " order by [Provider Name]"

        ProvidersBox.DataSource = LoadDataView(sql)
        ProvidersBox.DisplayMember = "Provider Name"
        ProvidersBox.ValueMember = "UPIN"
        Job("Ready")
    End Sub

    Private Sub LoadLABox()
        Dim sql As String
        Job("Loading Local Authorities...")

        If RegionBox.SelectedValue.ToString = "<All>" Then
            sql = "Select '<All>' as LA_Code, '<All>' as LA_Name, '<All>' as Region_Code from Global_Settings_Upgrade UNION ALL " & _
                  "Select * from Local_Authorities order by LA_Name"
        Else
            sql = "Select '<All>' as LA_Code, '<All>' as LA_Name, '<All>' as Region_Code from Global_Settings_Upgrade UNION ALL " & _
                  "Select * from Local_Authorities where Region_Code = '" & RegionBox.SelectedValue.ToString & "' order by LA_Name"

        End If

        LABox.DataSource = LoadDataView(sql)
        LABox.DisplayMember = "LA_Name"
        LABox.ValueMember = "LA_Code"
        LABox.Refresh()

    End Sub


    Private Sub HidePanels()

        'add panels here---
        ButtonPanel.Visible = False
        ProviderPanel.Visible = False
        LAPanel.Visible = False
        RegionPanel.Visible = False
        PeriodPanel.Visible = False
        SchoolsTickPanel.Visible = False
        PrintPanel.Visible = False
        NationalProvidersPanel.Visible = False
        ShadowPanel.Visible = False

        ReportLabel.Text = "Please make a selection on the left"
    End Sub

    Private Sub TheTreeView_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TheTreeView.AfterSelect
        'handle the headers first
        If TheTreeView.SelectedNode.Name = "ExportToExcel" Then
            '  Me.Close()
            ExcelExportForm.MdiParent = MainForm
            ExcelExportForm.Dock = DockStyle.Fill
            ExcelExportForm.Show()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ShadowAllocationsNode" Then
            HidePanels()
            ShadowPanel.Visible = True
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Shadow Allocations"
            Exit Sub
        End If


        If TheTreeView.SelectedNode.Name = "MainReportNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "VolumesReportNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "FundingReportsNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ExceptionReporstNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ChartNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "VolumesChartNode" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "Custom_Reports" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "Archived_Reports" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "Current_Reports" Then
            HidePanels()
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "GAGNode" Then
            HidePanels()
            Exit Sub
        End If

        'now handle the reports
        If TheTreeView.SelectedNode.Name = "VRRegionLANode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Learner Volumes Report by Region and Local Authority"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "LASummary" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Local Authority Summary"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "VRProviderNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Learner Volumes Report by Provider"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "VRSummaryNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Learner Volumes Report Summary"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "Bursary1619Node" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "16-19 Provider Bursary Allocations by Region and Local Authority"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ERProvidersNoPlanNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Exception Report - Providers with No Plan"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "PaymentSchedule" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            'SchoolsTickPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Payment Schedule"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "CCMPaymentSchedule" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "CCM Payment Schedule"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "LearnerVolumeChart" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Planned Learner Volumes Chart"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "App1ProvNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LABox.SelectedValue = "<All>"
            LAPanel.Visible = False
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text

            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "App1LANode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            'find the LA that belongs to this LA

            ProviderPanel.Visible = False
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text

            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "RegionalLearnerBreakdown" Then
            HidePanels()
            PeriodPanel.Visible = True
            NationalProvidersPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = "Regional Learner Volume Breakdown Report"
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ProvDetailsNode" Then
            HidePanels()
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If

        'Previous VB before changes to Node and report names
        'Spooner R 15/03/2012
        'If TheTreeView.SelectedNode.Name = "GAGAnnexANode" Then
        '    HidePanels()
        '    PeriodPanel.Visible = True
        '    RegionPanel.Visible = True
        '    LAPanel.Visible = True
        '    ProviderPanel.Visible = True
        '    ButtonPanel.Visible = True

        '    ReportLabel.Text = TheTreeView.SelectedNode.Text
        '    Exit Sub
        'End If

        'Previous VB before changes to Node and report names
        'Spooner R 15/03/2012
        'If TheTreeView.SelectedNode.Name = "GAGAnnexCNode" Then
        '    HidePanels()
        '    PeriodPanel.Visible = True
        '    RegionPanel.Visible = True
        '    LAPanel.Visible = True
        '    ProviderPanel.Visible = True
        '    ButtonPanel.Visible = True

        '    ReportLabel.Text = TheTreeView.SelectedNode.Text
        '    Exit Sub
        'End If

        'New VB to reflect Node and report names
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexADraftNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If


        'New VB to reflect Node and report names
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexAFinalNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If

        'New VB to reflect Node and report names
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexCDraftNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If

        'New VB to reflect Node and report names
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexCFinalNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "GAGScheduleNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True

            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If

        If TheTreeView.SelectedNode.Name = "ConvertGagIndicativeNode" Then
            HidePanels()
            PeriodPanel.Visible = True
            RegionPanel.Visible = True
            LAPanel.Visible = True
            ProviderPanel.Visible = True
            ButtonPanel.Visible = True
            ReportLabel.Text = TheTreeView.SelectedNode.Text
            Exit Sub
        End If


        Try
            If TheTreeView.SelectedNode.Parent.Name = "Current_Reports" Or TheTreeView.SelectedNode.Parent.Name = "Archived_Reports" Then
                HidePanels()
                ' PeriodPanel.Visible = True
                RegionPanel.Visible = True
                LAPanel.Visible = True

                'should the providerpanel be visible
                Dim qview As DataView
                qview = LoadDataView("select top 1 * from RD_Published_DataTable_Fields where Report_ID = '" & TheTreeView.SelectedNode.Name & "'")
                If qview.Count = 0 Then
                    ProviderPanel.Visible = True

                End If

                ButtonPanel.Visible = True
                ReportLabel.Text = TheTreeView.SelectedNode.Text
                Exit Sub

            End If
        Catch ex As Exception
            'this node has no parent
            HidePanels()
        End Try


    End Sub

    Private Sub LoadPlannedLearnerVolumesChart()
        'load the report
        Dim sql As String
        Dim WhereClause As String
        Dim ReportHeader, ReportHeader2, ReportHeader3 As String

        ReportHeader = "Period - " & DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name")
        ReportHeader2 = "Region - " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name") & ", Local Authority - " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")
        ReportHeader3 = "Provider - " & DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")

        WhereClause = "Where "

        If PeriodPanel.Visible = True Then
            WhereClause = WhereClause + "Period_ID = '" & PeriodBox.SelectedValue & "'"
        End If

        If RegionPanel.Visible = True Then
            If RegionBox.SelectedValue.ToString <> "<All>" Then
                If WhereClause <> "Where " Then
                    WhereClause = WhereClause + " and"
                End If
                WhereClause = WhereClause + " Regions.Region_Code = '" & RegionBox.SelectedValue & "'"
            End If
        End If

        If LAPanel.Visible = True Then
            If LABox.SelectedValue.ToString <> "<All>" Then
                If WhereClause <> "Where " Then
                    WhereClause = WhereClause + " and"
                End If
                WhereClause = WhereClause + " Local_Authorities.LA_Code = '" & LABox.SelectedValue & "'"
            End If
        End If

        If ProviderPanel.Visible = True Then
            If ProvidersBox.SelectedValue.ToString <> "0" Then
                If WhereClause <> "Where " Then
                    WhereClause = WhereClause + " and"
                End If
                WhereClause = WhereClause + " Young_Peoples_Volumes.RID in (Select RID from Relationships where UPIN = '" & ProvidersBox.SelectedValue & "')"
            End If
        End If


        If WhereClause = "Where " Then
            WhereClause = ""
        End If

        sql = "SELECT [Young_Peoples_Volumes].[Total Learners], [Young_Peoples_Volumes].[Total Foundation Learning (provision below Level 2)], " & _
"[Young_Peoples_Volumes].[FL Diplomas], [Young_Peoples_Volumes].[Foundation Learning Programmes], " & _
"[Young_Peoples_Volumes].[FL Components or Stand Alone (JACQA Approved], " & _
"[Young_Peoples_Volumes].[FL Other], [Young_Peoples_Volumes].[Total Learning at Level 2], " & _
"[Young_Peoples_Volumes].[L2 Diplomas], [Young_Peoples_Volumes].[L2 GCSEs/General Qualifications], " & _
"[Young_Peoples_Volumes].[L2 Components or Stand Alone (JACQA Approved], " & _
"[Young_Peoples_Volumes].[L2 Other], [Young_Peoples_Volumes].[Total Learning at Level 3], " & _
"[Young_Peoples_Volumes].[L3 Diplomas], [Young_Peoples_Volumes].[L3 A levels/General Qualifications], " & _
"[Young_Peoples_Volumes].[L3 Components or Stand Alone (JACQA Approved], [Young_Peoples_Volumes].[L3 Other], " & _
"[Young_Peoples_Volumes].[Total 16-18 learners on Apprenticeships], [Young_Peoples_Volumes].[Total 16-18 learners on advanced Apprenticeships], " & _
"[Providers].[UPIN], [Providers].[Provider_Name], [Regions].[Region_Code], [Regions].[Region_Name], " & _
"[Local_Authorities].[LA_Code], [Local_Authorities].[LA_Name] " & _
" FROM   ((([TheStore].[dbo].[Young_Peoples_Volumes] [Young_Peoples_Volumes] INNER JOIN [TheStore].[dbo].[Relationships] " & _
"[Relationships] ON [Young_Peoples_Volumes].[RID]=[Relationships].[RID]) INNER JOIN [TheStore].[dbo].[Providers] " & _
"[Providers] ON [Relationships].[UPIN]=[Providers].[UPIN]) INNER JOIN [TheStore].[dbo].[Local_Authorities] " & _
"[Local_Authorities] ON [Relationships].[LA_Code]=[Local_Authorities].[LA_Code]) INNER JOIN [TheStore].[dbo].[Regions] [Regions] ON " & _
"[Local_Authorities].[Region_Code]=[Regions].[Region_Code] " & WhereClause & _
" ORDER BY [Regions].[Region_Code], [Local_Authorities].[LA_Code] "


        ShowReport(ReportLabel.Text, "PlannedLearnerVolumesChart.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", ReportHeader, "ReportHeader1", ReportHeader2, "ReportHeader2", ReportHeader3, "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
    End Sub

    Private Sub RegionBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegionBox.SelectedIndexChanged
        LoadLABox()
    End Sub

    Private Sub LABox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LABox.SelectedIndexChanged
        LoadProvidersBox()

        Try
            If TheTreeView.SelectedNode.Name = "App1LANode" Then
                ChangeLAProvider()

            End If
        Catch ex As Exception

        End Try


    End Sub

    Private Sub LoadVolumesReport()
        'load the report
        Dim sql As String
        Dim WhereClause As String
        Dim ReportHeader As String

        ReportHeader = "Period - " & DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name") & _
                       ", Region - " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name") & _
                       ", Local Authority - " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")

        WhereClause = "Where "

        If PeriodPanel.Visible = True Then
            WhereClause = WhereClause + "Periods.Period_ID = '" & PeriodBox.SelectedValue & "'"
        End If

        If RegionPanel.Visible = True Then
            If RegionBox.SelectedValue.ToString <> "<All>" Then
                If WhereClause <> "Where " Then
                    WhereClause = WhereClause + " and"
                End If
                WhereClause = WhereClause + " Regions.Region_Code = '" & RegionBox.SelectedValue & "'"
            End If
        End If

        If LAPanel.Visible = True Then
            If LABox.SelectedValue.ToString <> "<All>" Then
                If WhereClause <> "Where " Then
                    WhereClause = WhereClause + " and"
                End If
                WhereClause = WhereClause + " Local_Authorities.LA_Code = '" & LABox.SelectedValue & "'"
            End If
        End If

        If WhereClause = "Where " Then
            WhereClause = ""
        End If

        sql = "SELECT     Local_Authorities.LA_Code, Local_Authorities.LA_Name, Providers.UPIN, Providers.Provider_Name, Regions.Region_Code, Regions.Region_Name, " & _
"                      Young_Peoples_Volumes.[Total Learners], Young_Peoples_Volumes.[Total Foundation Learning (provision below Level 2)], " & _
"                      Young_Peoples_Volumes.[FL Diplomas], Young_Peoples_Volumes.[Foundation Learning Programmes], " & _
"                      Young_Peoples_Volumes.[FL Components or Stand Alone (JACQA Approved], Young_Peoples_Volumes.[FL Other], " & _
"                      Young_Peoples_Volumes.[Total Learning at Level 2], Young_Peoples_Volumes.[L2 Diplomas], " & _
"                      Young_Peoples_Volumes.[L2 GCSEs/General Qualifications], Young_Peoples_Volumes.[L2 Components or Stand Alone (JACQA Approved], " & _
"                      Young_Peoples_Volumes.[L2 Other], Young_Peoples_Volumes.[Total Learning at Level 3], Young_Peoples_Volumes.[L3 Diplomas], " & _
"                      Young_Peoples_Volumes.[L3 A levels/General Qualifications], Young_Peoples_Volumes.[L3 Components or Stand Alone (JACQA Approved], " & _
"                      Young_Peoples_Volumes.[L3 Other], Young_Peoples_Volumes.[Total 16-18 learners on Apprenticeships], " & _
"                      Young_Peoples_Volumes.[Total 16-18 learners on advanced Apprenticeships] " & _
"FROM         Young_Peoples_Volumes INNER JOIN " & _
"                      Relationships ON Young_Peoples_Volumes.RID = Relationships.RID INNER JOIN " & _
"                      Providers ON Relationships.UPIN = Providers.UPIN INNER JOIN " & _
"                      Local_Authorities ON Relationships.LA_Code = Local_Authorities.LA_Code INNER JOIN " & _
"                      Periods ON Young_Peoples_Volumes.Period_ID = Periods.Period_ID INNER JOIN " & _
"                      Regions ON Local_Authorities.Region_Code = Regions.Region_Code " & WhereClause

        'sql = "SELECT     Young_Peoples_Volumes.*, Periods.Period_Name, Local_Authorities.LA_Name, Local_Authorities.LA_Code, Regions.*, Providers.* " & _
        '      "FROM         Young_Peoples_Volumes INNER JOIN " & _
        '              "Periods ON Young_Peoples_Volumes.Period_ID = Periods.Period_ID INNER JOIN " & _
        '              "Relationships ON Young_Peoples_Volumes.RID = Relationships.RID INNER JOIN " & _
        '              "Providers ON Relationships.UPIN = Providers.UPIN INNER JOIN " & _
        '              "Local_Authorities ON Relationships.LA_Code = Local_Authorities.LA_Code INNER JOIN " & _
        '              "Regions ON Local_Authorities.Region_Code = Regions.Region_Code " & _
        '              " " & WhereClause & " " & _
        '              " ORDER BY Regions.Region_Code, Local_Authorities.LA_Code"

        If TheTreeView.SelectedNode.Name = "VRRegionLANode" Then

            'LoadReport(ReportLabel.Text, "LearnerVolumesReport.rpt", sql, "ReportHeader", ReportHeader, "HideProviders", "True", "", "", "", "", "", "")
            ShowReport(ReportLabel.Text, "Planned16-18LearnerVolumes.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", ReportHeader, "ViewLA", "True", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
        Else
            'LoadReport(ReportLabel.Text, "LearnerVolumesReport.rpt", sql, "ReportHeader", ReportHeader, "HideProviders", "False", "", "", "", "", "", "")
            ShowReport(ReportLabel.Text, "Planned16-18LearnerVolumes.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", ReportHeader, "ViewLA", "False", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
        End If

    End Sub

    Private Sub LoadVolumesSummaryReport()
        Dim sql As String
        Dim whereclauseA As String
        Dim whereclauseB As String
        Dim BigHeader As String
        Dim SmallHeader As String


        If RegionBox.SelectedValue.ToString = "<All>" Then
            If LABox.SelectedValue.ToString = "<All>" Then
                If ProvidersBox.SelectedValue = 0 Then
                    whereclauseA = "1=1"
                    whereclauseB = whereclauseA
                Else
                    whereclauseA = "UPIN = " & ProvidersBox.SelectedValue.ToString
                    whereclauseB = whereclauseA
                End If
            Else
                If ProvidersBox.SelectedValue = 0 Then
                    whereclauseA = "r.LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    whereclauseB = "RID in (select distinct RID from Relationships where LA_Code = '" & LABox.SelectedValue.ToString & "')"
                Else
                    whereclauseA = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and r.LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    whereclauseB = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and RID in (select distinct RID from Relationships where LA_Code = '" & LABox.SelectedValue.ToString & "')"
                End If
            End If
        Else
            If LABox.SelectedValue.ToString = "<All>" Then
                If ProvidersBox.SelectedValue = 0 Then
                    whereclauseA = "r.LA_Code in (select LA_Code from Local_Authorities where Region_Code = '" & RegionBox.SelectedValue.ToString & "')"
                    whereclauseB = "Provider_Region = '" & RegionBox.SelectedValue.ToString & "'"
                Else
                    whereclauseA = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and " & "r.LA_Code in (select LA_Code from Local_Authorities where Region_Code = '" & RegionBox.SelectedValue.ToString & "')"
                    whereclauseB = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and " & "Provider_Region = '" & RegionBox.SelectedValue.ToString & "'"
                End If
            Else
                If ProvidersBox.SelectedValue = 0 Then
                    whereclauseA = "r.LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    whereclauseB = "RID in (select distinct RID from Relationships where LA_Code = '" & LABox.SelectedValue.ToString & "')"
                Else
                    whereclauseA = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and r.LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    whereclauseB = "UPIN = " & ProvidersBox.SelectedValue.ToString & " and RID in (select distinct RID from Relationships where LA_Code = '" & LABox.SelectedValue.ToString & "')"
                End If
            End If
        End If

        sql = "select FLP.Field_Code, Order_Field, Field_Description, Category_Code, " & _
     "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line,  " & _
     "sum(Act89.Learners) as '2008/09 F05 Learners', sum(Act910.Learners) as '2009/10 F01 Learners', " & _
     "sum(VD.Learners) as '2010/11 Learners', sum(Achievements) as '2010/11 Achievements'  " & _
     "from Fields_List_Part as FLP  " & _
     "left outer join  " & _
     "(Select Field_Code, sum(Learners) as Learners, sum(Achievements) as Achievements " & _
     "from dbo.Volumes_Data d, Relationships r " & _
     "where " & whereclauseA & " " & _
     "and d.RID = r.RID and d.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
     "group by Field_Code) VD  " & _
     "on FLP.Field_Code = VD.Field_Code  " & _
     "left outer JOIN  " & _
     "( SELECT Field_Code, Sum(Learners) as Learners " & _
     "FROM [dbo].[YP_200809F05_Act_UPIN]  " & _
     "where " & whereclauseB & " " & _
     "group by Field_Code) Act89 " & _
     "on FLP.Field_Code = Act89.Field_Code  " & _
     "left outer JOIN  " & _
     "( SELECT Field_Code, Sum(Learners) as Learners " & _
     "FROM [dbo].[YP_200910F01_Act_UPIN]  " & _
     "where " & whereclauseB & " " & _
     "group by Field_Code) Act910 " & _
     "on FLP.Field_Code = Act910.Field_Code  " & _
     "group by " & _
     "FLP.Field_Code, Order_Field, Field_Description, Category_Code, " & _
     "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line " & _
     "order by order_field"

        '    sql = "select FLP.Field_Code, Order_Field, Field_Description , Category_Code, " & _
        '          "Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line, " & _
        '          "Learners as '2010/11 Learners', Achievements as '2010/11 Achievements' " & _
        '          "from Fields_List_Part as FLP " & _
        '          "left outer join " & _
        '          "(select sum(Learners) as Learners, sum(Achievements) as Achievements, Field_Code from " & _
        '         "Volumes_Data a, Relationships rel, Local_Authorities LA " & _
        '         "where " & _
        '        "a.RID = rel.RID and " & _
        '       "rel.LA_Code = LA.LA_Code " & _
        '      "and Period_ID = " & PeriodBox.SelectedValue.ToString & _
        '     whereclause & _
        '    "Group by Field_Code) VD " & _
        '      "on FLP.Field_Code = VD.Field_Code " & _
        '      "order by order_field"

        '  Clipboard.SetDataObject(sql)

        BigHeader = DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")

        If BigHeader = "<All>" Then
            BigHeader = "All Providers"
        End If

        If RegionBox.SelectedValue.ToString = "<All>" And LABox.SelectedValue.ToString = "<All>" Then
            SmallHeader = "For all Regions and Local Authorities"
        Else
            If RegionBox.SelectedValue.ToString = "<All>" Then
                SmallHeader = DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")
            Else
                If LABox.SelectedValue.ToString = "<All>" Then
                    SmallHeader = "For all Local Authorities in " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name")
                Else
                    SmallHeader = DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name") & " - " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")
                End If
            End If
        End If

        'LoadReport("Young People Learning Volumes", "VolumesReport.rpt", sql, "", "", "", "", _
        '               "ProviderName", BigHeader, "LocalAuthority", SmallHeader, "", "")
        ShowReport("Young People Learning Volumes", "VolumesReport.rdl", "TheStore", sql, "", "", "", "", "", "", "", "", _
                   "ProviderName", BigHeader, "LocalAuthority", SmallHeader, "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub LoadERProvidersNoPlanReport()
        Dim sql As String
        Dim whereclause As String
        Dim TheHeader As String

        whereclause = ""

        If LABox.SelectedValue <> "<All>" Then
            whereclause = "and LA_Code = '" & LABox.SelectedValue.ToString & "'"
        Else
            If RegionBox.SelectedValue <> "<All>" Then
                whereclause = "and Region_Code = '" & RegionBox.SelectedValue.ToString & "'"
            End If
        End If

        TheHeader = "Region - " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name") & _
                    ", Local Authority - " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")

        sql = "Select * from " & _
              "(select r.RID, reg.Region_Code, reg.Region_Name, r.LA_Code, LA.LA_Name, r.UPIN, p.Provider_Name, coalesce(a.Total,0) as Total  from relationships r left outer join " & _
              "(select RID, sum(Learners) as Total " & _
              "from volumes_data where Field_Code not in (1013,1001) " & _
              "Group by RID) a on r.RID = a.RID, " & _
              "Providers p, Local_Authorities LA, Regions reg " & _
              "where p.UPIN = r.UPIN " & _
              "and LA.LA_Code = r.LA_Code " & _
              "and reg.Region_Code = LA.Region_Code) b " & _
              "where Total = 0 " & whereclause



        'LoadReport(ReportLabel.Text, "ERProvidersNOPlan.rpt", sql, "ReportHeader", TheHeader, "", "", "", "", "", "", "", "")
        ShowReport(ReportLabel.Text, "ERProvidersNoPlan.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", TheHeader, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    

    Private Sub Appendix1ai()
        Dim SQL As String
        Dim TheDataView As DataView
        Dim StartDate, EndDate As String
        Dim Rid As String


        'This is a provider only report
        If ProvidersBox.SelectedValue = 0 Then
            If PrintIt = False Then
                MessageBox.Show("This is a provider only report.  Please change your selection to a provider", "Provider only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If

        If LABox.SelectedValue = "<All>" Then
            If PrintIt = False Then
                MessageBox.Show("This is a provider only report.  Please change the Local Authority.", "Provider only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If

        'get RID
        SQL = "select RID from Relationships where UPIN = '" & ProvidersBox.SelectedValue.ToString & "' and LA_Code = '" & LABox.SelectedValue.ToString & "'"
        TheDataView = LoadDataView(SQL)
        RID = TheDataView(0).Item("RID").ToString

        SQL = "select P.LA_Code, P.LA_Name, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
"PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners, P.UPIN, P.Provider_Name, P.Region_Name " & _
"from PaymentProfiles PP " & _
"left outer join All_Provider_Fields P on (pp.RID = P.RID), " & _
"     Budgets B " & _
"where  " & _
"B.Budget_ID = PP.Budget_ID " & _
"and B.Budget_ID in ('YPLRA', 'YPLRB') " & _
"and PP.RID = '" & RID & "' " & _
"and pp.Period_ID = " & PeriodBox.SelectedValue.ToString

        TheDataView = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
        StartDate = FormatDateTime(TheDataView(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataView(0).Item("End_Date"), DateFormat.LongDate)


        ShowReport("Appendix 1a(i)", "Appendix1a.rdl", "TheStore", SQL, "", "", "", "", "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", PrintIt, True)
        

    End Sub

    Private Sub Appendix1aii()
        Dim SQL As String
        Dim TheDataView As DataView

        Dim Rid As String


        'This is a provider only report
        If ProvidersBox.SelectedValue = 0 Then
            If PrintIt = False Then
                MessageBox.Show("This is a provider only report.  Please change your selection to a provider", "Provider only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If

        If LABox.SelectedValue = "<All>" Then
            If PrintIt = False Then
                MessageBox.Show("This is a provider only report.  Please change the Local Authority.", "Provider only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If

        'get RID
        SQL = "select RID from Relationships where UPIN = '" & ProvidersBox.SelectedValue.ToString & "' and LA_Code = '" & LABox.SelectedValue.ToString & "'"
        TheDataView = LoadDataView(SQL)
        Rid = TheDataView(0).Item("RID").ToString

        SQL = "select FLP.Field_Code, Order_Field, Field_Description, Category_Code, " & _
"Has_Achievement, Is_Total_Line, Is_Header_Line, Is_Auto_Total_Line, " & _
"Act89.Learners as '2008/09 F05 Learners', Act910.Learners as '2009/10 F01 Learners', " & _
"VD.Learners as '2010/11 Learners', Achievements as '2010/11 Achievements' " & _
"from Fields_List_Part as FLP " & _
" " & _
"left outer join " & _
"(Select * from dbo.Volumes_Data " & _
"where RID = '" & Rid & "' and Period_ID = " & PeriodBox.SelectedValue.ToString & ") VD " & _
"on FLP.Field_Code = VD.Field_Code " & _
" " & _
"left outer JOIN " & _
"( SELECT * " & _
" FROM [dbo].[YP_200809F05_Act] " & _
" where RID = '" & Rid & "') Act89 " & _
" on FLP.Field_Code = Act89.Field_Code " & _
"  " & _
" left outer JOIN " & _
"( SELECT * " & _
" FROM [dbo].[YP_200910F01_Act] " & _
" where RID = '" & Rid & "') Act910 " & _
" on FLP.Field_Code = Act910.Field_Code " & _
" " & _
"order by order_field"

        


        ShowReport("Appendix 1a(ii)", "Appendix1a2.rdl", "TheStore", SQL, "", "", "", "", "ProviderName", ProvidersBox.SelectedValue & " - " & ProvidersBox.Text, _
                   "LocalAuthority", LABox.Text & " - " & LABox.SelectedValue, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", PrintIt, False)
        
    End Sub

    Private Sub Appendix1bi()
        Dim SQL As String
        Dim TheDataView As DataView
        Dim StartDate, EndDate As String



        'This is a LA only report

        If LABox.SelectedValue = "<All>" Then
            If PrintIt = False Then
                MessageBox.Show("This is a Local Authority only report.  Please change the Local Authority.", "Local Authority only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If


        SQL = "select PP.LA_Code, LA.LA_Name, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
"PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners " & _
"from S6F_PaymentProfiles PP, dbo.Local_Authorities LA, Budgets B " & _
"where  " & _
"B.Budget_ID = PP.Budget_ID " & _
"and PP.LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
"and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
"and PP.LA_Code = LA.LA_Code " & _
" " & _
"union all " & _
" " & _
"select PP.LA_Code, LA.LA_Name, Period_ID, 'Other LA Funding' as Budget_Description,  " & _
"Allocation_Description, Allocation_ID, Month, Month_Total, FYMonth, 0 as Learners " & _
"from dbo.SEN_TPG_PaymentProfiles PP, dbo.Local_Authorities LA " & _
"Where PP.LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
"and Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
"and PP.LA_Code = LA.LA_Code "

        TheDataView = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
        StartDate = FormatDateTime(TheDataView(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataView(0).Item("End_Date"), DateFormat.LongDate)


        ShowReport("Appendix 1b(i)", "Appendix1b.rdl", "TheStore", SQL, "", "", "", "", "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", PrintIt, True)

    End Sub


    Private Sub Appendix1bii()
        Dim SQL, SQLsub As String


        'This is a LA only report

        If LABox.SelectedValue = "<All>" Then
            If PrintIt = False Then
                MessageBox.Show("This is a Local Authority only report.  Please change the Local Authority.", "Local Authority only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If


        SQL = "Select *, (Total_FY_Funding_S6F + SEN + TPG) as Total_LA_FY_Allocation from " & _
"(select sum(Learner_Funding + Diploma_Funding) as Learner_Funding,   " & _
"       sum(Total_ALS) as Total_ALS,   " & _
"       sum(coalesce(LLDD_Programme_Funding,0)) as LLDD_Programme_Funding,   " & _
"       sum(coalesce(LLDD_ALS,0)) as LLDD_ALS,   " & _
"       sum(Learner_Funding + Diploma_Funding + Total_ALS + coalesce(LLDD_Programme_Funding,0) + coalesce(LLDD_ALS,0)) as TotalFE   " & _
"from FE_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "') FE,   " & _
"(select sum(Baseline_Funding + Dip_Funding) as Baseline_Funding,   " & _
"       sum(ALS_Funding + Dip_ALS) as ALS_Funding,   " & _
"       sum(Pension_Funding) as Pension_Funding,   " & _
"       sum(Transitional_Prot_Funding) as Transitional_Prot_Funding,  " & _
"       sum(Baseline_Funding + ALS_Funding + Pension_Funding + Transitional_Prot_Funding + Total_Dip_Funding) as TotalS6F   " & _
"from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "') S6F,   " & _
"(select sum(Total_Funding_4Months) as Total_Funding_4Months,  " & _
"       sum(Total_Funding_8Months) as Total_Funding_8Months,   " & _
"       sum(Total_Funding_4Months + Total_Funding_8Months) as Total_FY_Funding_S6F " & _
"from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "') FY,   " & _
"(SELECT sum(SEN) as SEN,   " & _
"       sum(TPG) as TPG   " & _
"FROM SEN_TPG_Funding  where LA_Code = '" & LABox.SelectedValue.ToString & "') ST "

        SQLsub = "Select * from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "'"


        ShowReport("Appendix 1b(ii)", "Appendix_1bii.rdl", "TheStore", SQL, "", "", "", "", "ReportHeader", RegionBox.Text & ", " & LABox.Text & " - " & LABox.SelectedValue.ToString, _
                   "Academic_Year_Text", " " & PeriodBox.Text, "Financial_Year_Text", "", "", "", "", "", "", "", "", "", "", "", "", "", "S6F_Summary", SQLsub, PrintIt, True)
       


    End Sub

    Private Sub Appendix1c()
        Dim SQL As String
        Dim RID As String
        Dim TheDataView As DataView
        Dim StartDate, EndDate As String


        If LABox.SelectedValue = "<All>" Then
            If PrintIt = False Then
                MessageBox.Show("This is a provider or Local Authority Level report only.  Please change the Local Authority.", "Local Authority cannot be All only report", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Exit Sub
        End If


        If ProvidersBox.SelectedValue <> 0 Then
            'get RID
            SQL = "select RID from Relationships where UPIN = '" & ProvidersBox.SelectedValue.ToString & "' and LA_Code = '" & LABox.SelectedValue.ToString & "'"
            TheDataView = LoadDataView(SQL)
            RID = TheDataView(0).Item("RID").ToString

            SQL = "select P.LA_Code, P.LA_Name, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
"PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners, P.UPIN, P.Provider_Name, P.Region_Name " & _
"from PaymentProfiles PP " & _
"left outer join All_Provider_Fields P on (pp.RID = P.RID), " & _
"     Budgets B " & _
"where  " & _
"B.Budget_ID = PP.Budget_ID " & _
"and B.Budget_ID in ('YPLRN') " & _
"and pp.RID = '" & RID & "' " & _
"and pp.Period_ID = " & PeriodBox.SelectedValue

        Else
            SQL = "select P.LA_Code, P.LA_Name, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
"PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners, P.UPIN, P.Provider_Name, P.Region_Name " & _
"from PaymentProfiles PP " & _
"left outer join All_Provider_Fields P on (pp.RID = P.RID), " & _
"     Budgets B " & _
"where  " & _
"B.Budget_ID = PP.Budget_ID " & _
"and B.Budget_ID in ('YPLRN') " & _
"and p.LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
"and pp.Period_ID = " & PeriodBox.SelectedValue

        End If

        TheDataView = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
        StartDate = FormatDateTime(TheDataView(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataView(0).Item("End_Date"), DateFormat.LongDate)


        ShowReport("Appendix 1c", "Appendix1c.rdl", "TheStore", SQL, "", "", "", "", "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", PrintIt, True)
       

    End Sub

    Private Sub PaymentSchedule()
        Dim sql, StartDate, EndDate As String
        Dim TheDataview As DataView
        Dim RIDS As String = ""
        ' Dim DLFLearners, SchoolLearners, LLDDLearners As Integer
        Job("Loading Payment Schedule...")

        If ProvidersBox.SelectedValue = 0 Then
            If LABox.SelectedValue = "<All>" Then
                If RegionBox.SelectedValue = "<All>" Then
                    RIDS = "Select RID from All_Provider_Fields where lead = 1"
                Else
                    RIDS = "Select RID from All_Provider_Fields where Region_Code = '" & RegionBox.SelectedValue & "' and Lead = 1"
                End If
            Else
                RIDS = "Select RID from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "' and Lead = 1"
            End If
        Else
            RIDS = "'" & GetRID(ProvidersBox.SelectedValue) & "'"
        End If


            sql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
                  "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
                  "from PaymentProfilesV2 PP, " & _
                  "Relationships R, " & _
                  "Providers P, " & _
                  "Budgets B, " & _
                  "Allocation_Lines A " & _
                  "where R.RID = PP.RID " & _
                  "and P.UPIN = R.UPIN " & _
                  "and A.Allocation_ID = PP.Allocation_ID " & _
                  "and B.Budget_ID = A.Budget_ID  " & _
                  "and pp.RID in (" & RIDS & ") " & _
                  "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & _
                  "group by " & _
                  "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month"

            TheDataview = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
            StartDate = FormatDateTime(TheDataview(0).Item("Start_Date"), DateFormat.LongDate)
            EndDate = FormatDateTime(TheDataview(0).Item("End_Date"), DateFormat.LongDate)



            ShowReport("Payment Schedule for " & ProvidersBox.Text, "PaymentSchedule.rdl", "TheStore", sql, "", "", "", "", _
                       "LAHeader", DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name"), _
                       "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                       "ProviderHeader", ProvidersBox.Text & " (UPIN " & ProvidersBox.SelectedValue & ")", _
                       "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
    End Sub

    Private Sub PaymentSchedule_OLD()
        Dim sql, schoolsql, othersql, fesql, StartDate, EndDate, HeaderLine, sql2, RID, wherepart, LAHeader As String
        '  Dim DLFLearners, SchoolLearners, LLDDLearners As Integer
        Dim TheDataview As DataView

        LAHeader = ""

        If ProvidersBox.SelectedValue <> 0 Then
            RID = ""
            'check to see if I should get a RID or can I just use the UPIN

            If LABox.SelectedValue <> "<All>" Then
                'get RID
                sql2 = "select RID from Relationships where UPIN = '" & ProvidersBox.SelectedValue.ToString & "' and LA_Code = '" & LABox.SelectedValue.ToString & "'"
                TheDataview = LoadDataView(sql2)
                RID = TheDataview(0).Item("RID").ToString
                wherepart = "and pp.RID = '" & RID & "' "
            Else
                wherepart = "and R.UPIN = " & ProvidersBox.SelectedValue.ToString & " "
                If RegionBox.SelectedValue <> "<All>" Then
                    wherepart = wherepart & " and LA.Region_Code = '" & RegionBox.SelectedValue.ToString & "' "
                End If
            End If

            If SchoolCheckBox.Checked = True Then
                wherepart = wherepart & "and pp.Allocation_ID in (select Allocation_ID from allocation_lines where Budget_ID = 'YPLRC') "
            End If


            sql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
              "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
              "from PaymentProfiles PP, " & _
              "Local_Authorities LA, " & _
              "Relationships R, " & _
              "Providers P, " & _
              "Budgets B " & _
              "where R.RID = PP.RID " & _
              "and P.UPIN = R.UPIN " & _
              "and B.Budget_ID = PP.Budget_ID  " & _
              "and r.La_Code = LA.LA_Code " & _
              wherepart & _
              "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & _
              "group by " & _
              "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month"


            HeaderLine = DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")
            LAHeader = DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name")

        Else

            If LABox.SelectedValue <> "<All>" Then
                fesql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
                      "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
                      "from PaymentProfiles PP, " & _
                      "     Relationships R, " & _
                      "     Providers P, " & _
                      "     Budgets B " & _
                      "where R.RID = PP.RID " & _
                      "and P.UPIN = R.UPIN " & _
                      "and B.Budget_ID = PP.Budget_ID " & _
                      "and Allocation_ID not in ('YPC01', 'YPC02', 'YPC05', 'YPC06', 'YPC08', 'YPC09') " & _
                      "and r.LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
                      "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
                      "group by " & _
                      "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                schoolsql = "select PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
                      "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
                      "from S6F_PaymentProfiles PP, " & _
                      "     Budgets B " & _
                      "where B.Budget_ID = PP.Budget_ID " & _
                      "and PP.LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
                      "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
                      "group by " & _
                      "PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                othersql = "select LA_Code, Period_ID, 'Other LA Funding' as Budget_Description,  " & _
                      "Allocation_Description, Allocation_ID, Month, Month_Total, FYMonth, 0 as Learners " & _
                      "from dbo.SEN_TPG_PaymentProfiles " & _
                      "Where LA_Code = '" & LABox.SelectedValue.ToString & "' " & _
                      "and Period_ID = " & PeriodBox.SelectedValue.ToString

                If SchoolCheckBox.Checked = False Then
                    sql = fesql & " union all " & schoolsql & " union all " & othersql
                Else
                    sql = schoolsql & " union all " & othersql
                End If

                HeaderLine = DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")
            Else
                If RegionBox.SelectedValue <> "<All>" Then

                    fesql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
    "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
    "from PaymentProfiles PP, " & _
    "     Relationships R, " & _
    "     Providers P, " & _
    "     Budgets B, " & _
    "     Local_Authorities LA " & _
    "where R.RID = PP.RID " & _
    "and P.UPIN = R.UPIN " & _
    "and B.Budget_ID = PP.Budget_ID " & _
    "and r.La_Code = LA.LA_Code " & _
    "and Allocation_ID not in ('YPC01', 'YPC02', 'YPC05', 'YPC06', 'YPC08', 'YPC09') " & _
    "and LA.Region_Code = '" & RegionBox.SelectedValue.ToString & "' " & _
    "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "group by " & _
    "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                    schoolsql = "select PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
    "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
    "from S6F_PaymentProfiles PP, " & _
    "     Budgets B, " & _
    "     Local_Authorities LA " & _
    "where B.Budget_ID = PP.Budget_ID " & _
    "and PP.La_Code = LA.LA_Code " & _
    "and LA.Region_Code = '" & RegionBox.SelectedValue.ToString & "' " & _
    "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "group by " & _
    "PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                    othersql = "select LA.LA_Code, Period_ID, 'Other LA Funding' as Budget_Description, " & _
    "Allocation_Description, Allocation_ID, Month, Month_Total, FYMonth, 0 as Learners " & _
    "from dbo.SEN_TPG_PaymentProfiles, Local_Authorities LA " & _
    "Where LA.Region_Code = '" & RegionBox.SelectedValue.ToString & "' " & _
    "and Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "and SEN_TPG_PaymentProfiles.La_Code = LA.LA_Code"

                    If SchoolCheckBox.Checked = False Then
                        sql = fesql & " union all " & schoolsql & " union all " & othersql
                    Else
                        sql = schoolsql & " union all " & othersql
                    End If

                    HeaderLine = DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name")
                    'ReportHeader = "Payment Schedule - Regional Overview"
                Else

                    fesql = "select r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
    "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
    "from PaymentProfiles PP, " & _
    "     Relationships R, " & _
    "     Providers P, " & _
    "     Budgets B, " & _
    "     Local_Authorities LA " & _
    "where R.RID = PP.RID " & _
    "and P.UPIN = R.UPIN " & _
    "and B.Budget_ID = PP.Budget_ID " & _
    "and r.La_Code = LA.LA_Code " & _
    "and Allocation_ID not in ('YPC01', 'YPC02', 'YPC05', 'YPC06', 'YPC08', 'YPC09') " & _
    "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "group by " & _
    "r.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                    schoolsql = "select PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
    "Sum(PP.Month_Total) as Month_Total, 0 as FYMonth, sum(PP.Learner_Numbers) as Learners " & _
    "from S6F_PaymentProfiles PP, " & _
    "     Budgets B, " & _
    "     Local_Authorities LA " & _
    "where B.Budget_ID = PP.Budget_ID " & _
    "and PP.La_Code = LA.LA_Code " & _
    "and pp.Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "group by " & _
    "PP.LA_Code, PP.Period_ID, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month "

                    othersql = "select LA.LA_Code, Period_ID, 'Other LA Funding' as Budget_Description, " & _
    "Allocation_Description, Allocation_ID, Month, Month_Total, FYMonth, 0 as Learners " & _
    "from dbo.SEN_TPG_PaymentProfiles, Local_Authorities LA " & _
    "where Period_ID = " & PeriodBox.SelectedValue.ToString & " " & _
    "and SEN_TPG_PaymentProfiles.La_Code = LA.LA_Code"

                    If SchoolCheckBox.Checked = False Then
                        sql = fesql & " union all " & schoolsql & " union all " & othersql
                    Else
                        sql = schoolsql & " union all " & othersql
                    End If

                    HeaderLine = "National"

                End If
            End If

        End If


        TheDataview = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
        StartDate = FormatDateTime(TheDataview(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataview(0).Item("End_Date"), DateFormat.LongDate)

        ShowReport("Payment Schedule for " & HeaderLine, "PaymentSchedule.rdl", "TheStore", sql, "", "", "", "", _
                   "LAHeader", LAHeader, _
                   "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "ProviderHeader", HeaderLine, _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)
    End Sub

    Private Sub ProviderDetailsReport()

        Dim sql As String
        Dim filtersql As String = ""
        Dim ReportHeader As String

        sql = "select distinct " & _
                "Period_ID, " & _
                "Period_Name, " & _
                "Prov.UPIN, " & _
                "Prov.Provider_Name, " & _
                "Provider_Type_Code, " & _
                "Provider_Type_Description, " & _
                "URN, " & _
                "Establishment_Number, " & _
                "UAI, " & _
                "Converter, " & _
                "Converter_Date, " & _
                "Contract_Number, " & _
                "Parent_Contract_Number, " & _
                "Region_Code, " & _
                "Region_Name, " & _
                "Budget_ID, " & _
                "Budget_Description, " & _
                "Pre_UPIN, " & _
                "Pre.Provider_Name as Pre_Provider_Name " & _
                "from All_Provider_Fields Prov " & _
                " " & _
                "left outer join " & _
                " " & _
                "(select p.UPIN, pp.Pre_UPIN, p.Provider_Name from  " & _
                "dbo.Predecessor_Providers pp, " & _
                "Providers P " & _
                "where pp.Pre_UPIN = p.UPIN) pre on (pre.UPIN = prov.UPIN), " & _
                " " & _
                "(select distinct  " & _
                "a.Period_ID,  " & _
                "Period_Name, " & _
                "RID, " & _
                "b.Budget_ID, " & _
                "Budget_Description " & _
                "from allocations a, Allocation_Lines al, Budgets b, Periods p " & _
                "where a.status = 'Approved' " & _
                "and a.Allocation_ID = al.Allocation_ID " & _
                "and al.Budget_ID = b.Budget_ID " & _
                "and a.Allocation <> 0 " & _
                "and p.period_ID = a.Period_ID) Alloc  " & _
                " " & _
                "where Prov.RID = Alloc.RID"

        If ProvidersBox.SelectedValue = 0 Then
            If LABox.SelectedValue = "<All>" Then
                If RegionBox.SelectedValue = "<All>" Then
                    'All records
                    ReportHeader = "Showing all records"
                Else
                    'A region has been selected
                    filtersql = " and Region_Code = '" & RegionBox.SelectedValue & "'"
                    ReportHeader = "Showing records for " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name")

                End If
            Else
                'a LA has been selected
                filtersql = " and prov.UPIN in (Select distinct UPIN from relationships where LA_Code = '" & LABox.SelectedValue & "')"
                ReportHeader = "Showing records for " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")
            End If
        Else
            'a provider has been selected
            filtersql = " and Prov.UPIN = " & ProvidersBox.SelectedValue
            ReportHeader = "Showing records for " & DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")
        End If

        sql = sql & filtersql

        ShowReport("Provider Details Report", "Provider_Details.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", ReportHeader, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub ShadowAllocations()

        'check if the provider selected matches the report
        'ISPs
        If ShadowReportBox.SelectedIndex = 0 Then


            If ProvidersBox.SelectedValue <> 0 Then

                Dim TheView As DataView

                TheView = LoadDataView("Select Provider_Subtype_Code from Providers where UPIN = " & ProvidersBox.SelectedValue)

                If TheView(0)("Provider_Subtype_Code").ToString <> "18ISP" Then

                    MessageBox.Show("The report you selected can only be run for ISPs, please make sure you select an ISP from the provider box", _
                                    "Wrong type of provider selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Exit Sub

                End If

                With ReportViewerForm

                    '  .SelectBox.Items.Clear()

                    TheView = LoadDataView("Select Provider_name, UPIN from Providers where UPIN = " & ProvidersBox.SelectedValue)

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsISP"
                    .RunReport()


                End With
            Else
                'all providers
                Dim sql As String = ""
                Dim TheView As DataView
                If RegionBox.SelectedValue = "<All>" Then

                    sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '18ISP') and Lead = 1"
                Else

                    If LABox.SelectedValue = "<All>" Then

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '18ISP') and Lead = 1 and Region_Code = '" & RegionBox.SelectedValue.ToString & "'"
                    Else

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '18ISP') and Lead = 1 and LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    End If

                End If

                With ReportViewerForm

                    TheView = LoadDataView(sql)

                    If TheView.Count = 0 Then

                        MessageBox.Show("There are no ISP providers in the current selection", "No ISPs in selection", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsISP"
                    .RunReport()

                End With

            End If

        End If


        'Academy Special
        If ShadowReportBox.SelectedIndex = 1 Then


            If ProvidersBox.SelectedValue <> 0 Then

                Dim TheView As DataView

                TheView = LoadDataView("Select Provider_Subtype_Code from Providers where UPIN = " & ProvidersBox.SelectedValue)

                If TheView(0)("Provider_Subtype_Code").ToString <> "17NMF" Then

                    MessageBox.Show("The report you selected can only be run for Academy Special Providers, please make sure you select an Academy Special Provider from the provider box", _
                                    "Wrong type of provider selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Exit Sub

                End If

                With ReportViewerForm

                    ' .SelectBox.Items.Clear()

                    TheView = LoadDataView("Select Provider_name, UPIN from Providers where UPIN = " & ProvidersBox.SelectedValue)

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsAcadSpecial"
                    .RunReport()


                End With
            Else
                'all providers
                Dim sql As String = ""
                Dim TheView As DataView
                If RegionBox.SelectedValue = "<All>" Then

                    sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '17NMF') and Lead = 1"
                Else

                    If LABox.SelectedValue = "<All>" Then

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '17NMF') and Lead = 1 and Region_Code = '" & RegionBox.SelectedValue.ToString & "'"
                    Else

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '17NMF') and Lead = 1 and LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    End If

                End If

                With ReportViewerForm

                    TheView = LoadDataView(sql)

                    If TheView.Count = 0 Then

                        MessageBox.Show("There are no Academy Special Providers in the current selection", "No Academy Special Providers in selection", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsAcadSpecial"
                    .RunReport()

                End With

            End If

        End If

        'SSF non-maintained
        If ShadowReportBox.SelectedIndex = 2 Then


            If ProvidersBox.SelectedValue <> 0 Then

                Dim TheView As DataView

                TheView = LoadDataView("Select Provider_Subtype_Code from Providers where UPIN = " & ProvidersBox.SelectedValue)

                If TheView(0)("Provider_Subtype_Code").ToString <> "21NMS" Then

                    MessageBox.Show("The report you selected can only be run for Non-maintained Spcial Schools, please make sure you select a Non-maintained Spcial School from the provider box", _
                                    "Wrong type of provider selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Exit Sub

                End If

                With ReportViewerForm

                    '.SelectBox.Items.Clear()

                    TheView = LoadDataView("Select Provider_name, UPIN from Providers where UPIN = " & ProvidersBox.SelectedValue)

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsSSF"
                    .RunReport()


                End With
            Else
                'all providers
                Dim sql As String = ""
                Dim TheView As DataView
                If RegionBox.SelectedValue = "<All>" Then

                    sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '21NMS') and Lead = 1"
                Else

                    If LABox.SelectedValue = "<All>" Then

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '21NMS') and Lead = 1 and Region_Code = '" & RegionBox.SelectedValue.ToString & "'"
                    Else

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '21NMS') and Lead = 1 and LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    End If

                End If

                With ReportViewerForm

                    TheView = LoadDataView(sql)

                    If TheView.Count = 0 Then

                        MessageBox.Show("There are no Non-maintained Spcial Schools in the current selection", "No Non-maintained Spcial Schools in selection", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsSSF"
                    .RunReport()

                End With

            End If

        End If

        'SSF Maintained
        If ShadowReportBox.SelectedIndex = 3 Then


            If ProvidersBox.SelectedValue <> 0 Then

                Dim TheView As DataView

                TheView = LoadDataView("Select Provider_Subtype_Code from Providers where UPIN = " & ProvidersBox.SelectedValue)

                If TheView(0)("Provider_Subtype_Code").ToString <> "20MSS" Then

                    MessageBox.Show("The report you selected can only be run for Maintained Spcial Schools, please make sure you select a Maintained Spcial School from the provider box", _
                                    "Wrong type of provider selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Exit Sub

                End If

                With ReportViewerForm

                    '  .SelectBox.Items.Clear()

                    TheView = LoadDataView("Select Provider_name, UPIN from Providers where UPIN = " & ProvidersBox.SelectedValue)

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsSSF"
                    .RunReport()


                End With
            Else
                'all providers
                Dim sql As String = ""
                Dim TheView As DataView
                If RegionBox.SelectedValue = "<All>" Then

                    sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '20MSS') and Lead = 1"
                Else

                    If LABox.SelectedValue = "<All>" Then

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '20MSS') and Lead = 1 and Region_Code = '" & RegionBox.SelectedValue.ToString & "'"
                    Else

                        sql = "Select Provider_Name, UPIN from All_Provider_Fields where UPIN in (Select UPIN from Providers where Provider_Subtype_Code = '20MSS') and Lead = 1 and LA_Code = '" & LABox.SelectedValue.ToString & "'"
                    End If

                End If

                With ReportViewerForm

                    TheView = LoadDataView(sql)

                    If TheView.Count = 0 Then

                        MessageBox.Show("There are no Maintained Spcial Schools in the current selection", "No Maintained Spcial Schools in selection", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    End If

                    .SelectBox.DataSource = TheView
                    .SelectBox.ValueMember = "UPIN"
                    .SelectBox.DisplayMember = "Provider_Name"

                    .TheReportToRun = "ShadowAllocationsSSF"
                    .RunReport()

                End With

            End If

        End If

    End Sub

    Private Sub ViewReportBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewReportBut.Click
        'reset non standard buttons
        ReportViewerForm.PDFButton.Visible = False
        ReportViewerForm.SelectBox.Visible = False

        Try
            If TheTreeView.SelectedNode.Parent.Name = "Current_Reports" Or TheTreeView.SelectedNode.Parent.Name = "Archived_Reports" Then

                LoadCustomReport()
                Exit Sub

            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'has no parent
        End Try


        ShowLoadingForm()
        Job("Loading Report Data...")

        If TheTreeView.SelectedNode.Name = "VRRegionLANode" Or TheTreeView.SelectedNode.Name = "VRProviderNode" Then
            LoadVolumesReport()
        End If

        If TheTreeView.SelectedNode.Name = "VRSummaryNode" Then
            LoadVolumesSummaryReport()
        End If

        If TheTreeView.SelectedNode.Name = "ERProvidersNoPlanNode" Then
            LoadERProvidersNoPlanReport()
        End If

        If TheTreeView.SelectedNode.Name = "LASummary" Then
            LASummaryReport()
        End If

        If TheTreeView.SelectedNode.Name = "Bursary1619Node" Then
            Bursary1618Report()
        End If

        If TheTreeView.SelectedNode.Name = "PaymentSchedule" Then
            PaymentSchedule()
        End If

        If TheTreeView.SelectedNode.Name = "LearnerVolumeChart" Then
            LoadPlannedLearnerVolumesChart()
        End If

        If TheTreeView.SelectedNode.Name = "ProvDetailsNode" Then
            ProviderDetailsReport()
        End If

        ''Previous version
        ''Spooner R 15/03/2012
        'If TheTreeView.SelectedNode.Name = "GAGAnnexANode" Then
        '    ACAD_Load_GAG_AnnexA()
        'End If

        ''Previous version
        ''Spooner R 15/03/2012
        'If TheTreeView.SelectedNode.Name = "GAGAnnexCNode" Then
        '    ACAD_Load_GAG_AnnexC()
        'End If

        'New version
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexADraftNode" Then
            ACAD_Load_GAG_AnnexADraft()
        End If

        'New version
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexAFinalNode" Then
            ACAD_Load_GAG_AnnexAFinal()
        End If

        'New version
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexCDraftNode" Then
            ACAD_Load_GAG_AnnexCDraft()
        End If

        'New version
        'Spooner R 15/03/2012
        If TheTreeView.SelectedNode.Name = "GAGAnnexCFinalNode" Then
            ACAD_Load_GAG_AnnexCFinal()
        End If

        If TheTreeView.SelectedNode.Name = "ConvertGagIndicativeNode" Then
            ACAD_Load_Conv_GAG_Indic()
        End If

        If TheTreeView.SelectedNode.Name = "GAGScheduleNode" Then
            ACAD_Load_GAG_Schedule()
        End If

        If TheTreeView.SelectedNode.Name = "App1ProvNode" Then
            If PeriodBox.SelectedValue <> 2010111 Then
                HideLoadingForm()
                MessageBox.Show("This report was specifically designed for academic year 2010/11.  Please use the CCM Payment Schedule Report under Funding Reports", "2010/11 only", MessageBoxButtons.OK, MessageBoxIcon.Information)

                Exit Sub

            Else
                Appendix1()
            End If

        End If

        If TheTreeView.SelectedNode.Name = "App1LANode" Then
            If PeriodBox.SelectedValue <> 2010111 Then
                HideLoadingForm()
                MessageBox.Show("This report was specifically designed for academic year 2010/11.  Please use the CCM Payment Schedule Report under Funding Reports", "2010/11 only", MessageBoxButtons.OK, MessageBoxIcon.Information)

                Exit Sub
            Else
                Appendix1()
            End If

        End If

        If TheTreeView.SelectedNode.Name = "RegionalLearnerBreakdown" Then
            LoadRegionalLearnerReport()
        End If

        If TheTreeView.SelectedNode.Name = "CCMPaymentSchedule" Then
            CCMPaymentReport()

        End If

        If TheTreeView.SelectedNode.Name = "ShadowAllocationsNode" Then
            ShadowAllocations()

        End If


        HideLoadingForm()

    End Sub

    Private Sub CCMPaymentReport()

        Dim TheView As DataView
        Dim sql As String

        Dim TheFilter As String


        Select Case FilterBox.SelectedValue
            Case "Acade"
                TheFilter = "'Acade'"

            Case "Schoo"
                TheFilter = "'Schoo'"
            Case "Other"
                TheFilter = "select Provider_Type_Code from provider_types where Provider_Type_Code not in ('Acade','Schoo')"

            Case Else
                TheFilter = "select Provider_Type_Code from provider_types"

        End Select


        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code in (" & TheFilter & ") and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code in (" & TheFilter & ") and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code in (" & TheFilter & ") and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code in (" & TheFilter & ") and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code in (" & TheFilter & ") and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "CCM_Funding_Agreement"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With




        'Dim TheView As DataView
        'Dim ReportType As String
        'Dim RID As String
        'Dim SQL As String
        'Dim ProviderText As String
        'Dim PeriodText As String

        'If ProvidersBox.SelectedValue = 0 Then
        '    'All providers
        '    MessageBox.Show("This is a provider level report only, please select a provider", "Provider level reports", MessageBoxButtons.OK, MessageBoxIcon.Information)
        '    Exit Sub
        'Else
        '    'figure out if this is a LA or not
        '    TheView = LoadDataView("Select Provider_Type_Code, RID from all_provider_fields where UPIN = " & ProvidersBox.SelectedValue & " and lead = 1")
        '    If TheView(0).Item("Provider_Type_Code").ToString = "Local" Then
        '        ReportType = "LA"
        '    Else
        '        ReportType = "Provider"
        '    End If

        '    RID = TheView(0).Item("RID")
        'End If

        'SQL = "exec spCCM_Funding_Report " & PeriodBox.SelectedValue & ", '" & RID & "'"

        'ProviderText = ProvidersBox.SelectedValue & " - " & DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")

        'PeriodText = DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name")

        'ShowReport("CCM Payment Schedule", "CCM_Funding_Agreement.rdl", "TheStore", Sql, "", "", "", "", "ReportType", ReportType, "ProviderHeader", ProviderText, "Period", PeriodText, "", "", "", "", "", "", _
        '           "", "", "", "", "", "", "", "", False, False)


        'Dim SQL As String
        'Dim TheView As DataView
        'Dim PeriodText As String
        'Dim ProviderText As String
        'Dim ReportType As String
        'Dim SubSQL As String
        'Dim tsql As String
        'Dim StartDate As DateTime
        'Dim EndDate As DateTime
        'Dim rec As DataRowView
        'Dim RidView As DataView

        ''check if this is an academy
        'TheView = LoadDataView("Select Provider_Type_Code from all_provider_fields where UPIN = " & ProvidersBox.SelectedValue)

        'If TheView(0).Item("Provider_Type_Code").ToString = "Acade" Then

        '    SQL = "select min(month) as Start_Date, max(month) as End_Date from patterns where pattern_ID in ( " & _
        '        "select Pattern_ID from pattern_IDs where budget_ID in ( " & _
        '        "select Budget_ID from provider_funding_streams where Period_ID = " & PeriodBox.SelectedValue & " and UPIN = " & ProvidersBox.SelectedValue & ") " & _
        '        "and period_Id =" & PeriodBox.SelectedValue & ") "

        '    TheView = LoadDataView(SQL)
        'Else
        '    SQL = "select * from periods where Period_ID = " & PeriodBox.SelectedValue
        '    TheView = LoadDataView(SQL)
        'End If


        'PeriodText = "Period:  " & FormatDateTime(TheView(0).Item("Start_Date"), DateFormat.ShortDate) & " - " & FormatDateTime(TheView(0).Item("End_Date"), DateFormat.ShortDate)

        'StartDate = TheView(0).Item("Start_Date")
        'EndDate = TheView(0).Item("End_Date")

        'ProviderText = ProvidersBox.SelectedValue & " - " & DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")

        'SQL = "select d.*, p.UPIN, p.Provider_Name, p.Provider_Type_Code " & _
        '    "	from dbo.CCM_Payments d, " & _
        '    "	     all_provider_fields p " & _
        '    "where  " & _
        '    "d.RID = p.RID " & _
        '    "and p.Lead = 1 " & _
        '    "and ((TheMonth >= " & StartDate.ToOADate - 2 & " and TheMonth <= " & EndDate.ToOADate - 2 & ") or (TheMonth is null and period_id = " & PeriodBox.SelectedValue & ")) "

        ''        "and period_id = " & PeriodBox.SelectedValue & " " & _

        'ReportType = "Provider"

        'If ProvidersBox.SelectedValue = 0 Then
        '    'All providers
        '    If LABox.SelectedValue = "<All>" Then
        '        'All LAs
        '        If RegionBox.SelectedValue = "<All>" Then
        '            'All Regions
        '        Else
        '            SQL = SQL & "and d.RID in (Select RID from All_Provider_Fields where Region_Code = '" & RegionBox.SelectedValue & "' and Lead = 1)"
        '        End If
        '    Else
        '        SQL = SQL & "and d.RID in (Select RID from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "' and Lead = 1)"
        '    End If
        'Else
        '    'figure out if this is a LA or not
        '    TheView = LoadDataView("Select Provider_Type_Code from all_provider_fields where UPIN = " & ProvidersBox.SelectedValue)
        '    If TheView(0).Item("Provider_Type_Code").ToString = "Local" Then
        '        ReportType = "LA"

        '        'MessageBox.Show("Please note that a LA report can take some time to run.  The more schools the LA has the longer the report will take to complete.  So please be patient. ", "Click OK to continue...", MessageBoxButtons.OK, MessageBoxIcon.Information)

        '        'see if this will speed it up.  Calculate the RIDs one by one in a temp table and throw that to the report

        '        SubSQL = "select RID from all_provider_Fields where LA_Code = '" & LA_Code_From_UPIN(ProvidersBox.SelectedValue) & "' " & _
        '                 "and Provider_Type_Code in ('Schoo', 'Local') and lead = 1 "

        '        RidView = LoadDataView(SubSQL)

        '        ProgressBarForm.Show()
        '        ProgressBarForm.TheProgressBar.Minimum = 0
        '        ProgressBarForm.TheProgressBar.Maximum = RidView.Count
        '        ProgressBarForm.TheProgressBar.Step = 1
        '        ProgressBarForm.Refresh()

        '        For Each rec In RidView
        '            SubSQL = SQL

        '            'SubSQL = SubSQL & "and d.RID = '" & rec.Item("RID") & "' " & _
        '            '        "and (d.RID <> '" & GetRID(ProvidersBox.SelectedValue) & "' and d.[Deliverable Code] not in ( " & _
        '            '        "select DeliverableCode from dbo.CCM_Deliverables_MultiYear where Period_ID = " & PeriodBox.SelectedValue & " and allocation_id in (select allocation_ID from dbo.Allocation_Lines where Budget_ID not in ('YPLRC'))) " & _
        '            '        "or (d.RID = '" & GetRID(ProvidersBox.SelectedValue) & "')) "

        '            SubSQL = SubSQL & "and d.RID = '" & rec.Item("RID") & "' "

        '            If ProgressBarForm.TheProgressBar.Value = 0 Then
        '                RunNonQuery("drop table temp_" & CurrentUser.Name)

        '                tsql = "select * into temp_" & CurrentUser.Name & " from (" & SubSQL & ") a"
        '                RunNonQuery(tsql)
        '            Else
        '                tsql = "insert into temp_" & CurrentUser.Name & " " & SubSQL
        '                RunNonQuery(tsql)
        '            End If

        '            ProgressBarForm.TheProgressBar.PerformStep()
        '            ProgressBarForm.Refresh()
        '        Next

        '        If RidView.Count = 0 Then
        '            'the view is empty so just create a blank table
        '            SubSQL = SQL

        '            'SubSQL = SubSQL & "and d.RID = 'norid' " & _
        '            '        "and (d.RID <> '" & GetRID(ProvidersBox.SelectedValue) & "' and d.[Deliverable Code] not in ( " & _
        '            '        "select DeliverableCode from dbo.CCM_Deliverable_Codes where allocation_id in (select allocation_ID from dbo.Allocation_Lines where Budget_ID in ('YPLRA', 'YPLRB', 'YPLRD', 'YPLRJ'))) " & _
        '            '        "or (d.RID = '" & GetRID(ProvidersBox.SelectedValue) & "')) "

        '            SubSQL = SubSQL & "and d.RID = 'norid' "

        '            RunNonQuery("drop table temp_" & CurrentUser.Name)

        '            tsql = "select * into temp_" & CurrentUser.Name & " from (" & SubSQL & ") a"
        '            RunNonQuery(tsql)
        '        End If

        '        ProgressBarForm.Close()

        '    Else
        '        If DZ_ProviderType(ProvidersBox.SelectedValue.ToString) = "s6f" Then
        '            'this is a school, so dont show the school allocation, because it would have gone to the LA
        '            SQL = SQL & "and d.RID = '" & GetRID(ProvidersBox.SelectedValue) & "' " & _
        '                    "and d.[Deliverable Code] in (select DeliverableCode from dbo.CCM_Deliverable_Codes where allocation_id in (select allocation_ID from dbo.Allocation_Lines where Budget_ID in ('YPLRA', 'YPLRB', 'YPLRD', 'YPLRJ')))"
        '        Else
        '            SQL = SQL & "and d.RID = '" & GetRID(ProvidersBox.SelectedValue) & "' "
        '        End If

        '        RunNonQuery("drop table temp_" & CurrentUser.Name)

        '        tsql = "select * into temp_" & CurrentUser.Name & " from (" & SQL & ") a"
        '        RunNonQuery(tsql)

        '    End If
        'End If

        'If ReportType <> "Provider" Then
        '    SQL = "Select * from temp_" & CurrentUser.Name & " where Value <> 0"
        'End If


        'ShowReport("CCM Payment Schedule", "CCM_Funding_Agreement.rdl", "TheStore", SQL, "", "", "", "", "ReportType", ReportType, "ProviderHeader", ProviderText, "Period", PeriodText, "", "", "", "", "", "", _
        '           "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub CCMPaymentReport_OLD()
        Dim SQL As String
        Dim TheView As DataView
        Dim PeriodText As String
        Dim ProviderText As String
        Dim ReportType As String
        Dim SubSQL As String

        SQL = "select * from periods where Period_ID = " & PeriodBox.SelectedValue
        TheView = LoadDataView(SQL)
        PeriodText = "Period:  " & FormatDateTime(TheView(0).Item("Start_Date"), DateFormat.ShortDate) & " - " & FormatDateTime(TheView(0).Item("End_Date"), DateFormat.ShortDate)

        ProviderText = ProvidersBox.SelectedValue & " - " & DirectCast(ProvidersBox.SelectedItem, DataRowView).Item("Provider Name")

        SQL = "select d.*, p.Provider_Name, p.Provider_Type_Code " & _
            "	from dbo.CCM_Deliverables d, " & _
            "	     (select distinct UPIN, Provider_Name, Provider_Type_Code from all_provider_fields) p " & _
            "where  " & _
            "d.UPIN = p.UPIN " & _
            "and period_id = " & PeriodBox.SelectedValue & " "

        ReportType = "Provider"

        If ProvidersBox.SelectedValue = 0 Then
            'All providers
            If LABox.SelectedValue = "<All>" Then
                'All LAs
                If RegionBox.SelectedValue = "<All>" Then
                    'All Regions
                Else
                    SQL = SQL & "and d.UPIN in (Select distinct UPIN from All_Provider_Fields where Region_Code = '" & RegionBox.SelectedValue & "')"
                End If
            Else
                SQL = SQL & "and d.UPIN in (Select distinct UPIN from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "')"
            End If
        Else
            'figure out if this is a LA or not
            TheView = LoadDataView("Select Provider_Type_Code from all_provider_fields where UPIN = " & ProvidersBox.SelectedValue)
            If TheView(0).Item("Provider_Type_Code").ToString = "Local" Then
                ReportType = "LA"

                SubSQL = "select distinct UPIN from all_provider_Fields where LA_Code in " & _
                    "(select top 1 LA_Code from all_provider_fields where upin = " & ProvidersBox.SelectedValue & ") " & _
                    "and Provider_Type_Code in ('Schoo', 'Local')"

                SQL = SQL & "and d.UPIN in (" & SubSQL & ") " & _
                            "and (d.upin <> " & ProvidersBox.SelectedValue & " and d.[Deliverable Code] not in ( " & _
                            "select DeliverableCode from dbo.CCM_Deliverable_Codes where allocation_id in (select allocation_ID from dbo.Allocation_Lines where Budget_ID in ('YPLRA', 'YPLRB', 'YPLRD', 'YPLRJ'))) " & _
                            "or (d.upin = " & ProvidersBox.SelectedValue & ")) "

            Else
                If DZ_ProviderType(ProvidersBox.SelectedValue.ToString) = "s6f" Then
                    'this is a school, so dont show the school allocation, because it would have gone to the LA
                    SQL = SQL & "and d.UPIN = " & ProvidersBox.SelectedValue & " " & _
                            "and d.[Deliverable Code] in (select DeliverableCode from dbo.CCM_Deliverable_Codes where allocation_id in (select allocation_ID from dbo.Allocation_Lines where Budget_ID in ('YPLRA', 'YPLRB', 'YPLRD', 'YPLRJ')))"
                Else
                    SQL = SQL & "and d.UPIN = " & ProvidersBox.SelectedValue
                End If

            End If
        End If


        ShowReport("CCM Payment Schedule", "CCM_Funding_Agreement.rdl", "TheStore", SQL, "", "", "", "", "ReportType", ReportType, "ProviderHeader", ProviderText, "Period", PeriodText, "", "", "", "", "", "", _
                   "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub LoadRegionalLearnerReport()
        Dim sql As String
        Dim TheView As DataView
        Dim TheLead As String

        If NationalProviderBox.SelectedIndex = -1 Then
            Exit Sub
        End If

        sql = "select Lead, Region_Name from All_provider_Fields where lead = " & _
            "(select coalesce(max(lead),0) as Lead from all_provider_fields " & _
            "where upin = " & NationalProviderBox.SelectedValue & ") " & _
            "and UPIN = " & NationalProviderBox.SelectedValue

        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            TheLead = "No lead has been set up"
        Else
            If TheView(0).Item("Lead") = 0 Then
                TheLead = "No lead has been set up"
            Else
                TheLead = TheView(0).Item("Region_Name")
            End If
        End If

        sql = "select a.UPIN, Provider_Name, a.Region_Name, sum(vd.Learners) as Learners from dbo.Volumes_Data vd, All_Provider_Fields a " & _
                "where a.RID = vd.RID " & _
                "and period_id = " & PeriodBox.SelectedValue & " and a.UPIN = " & NationalProviderBox.SelectedValue & _
                " group by UPIN, Provider_Name, Region_Name"

        ShowReport("Regional Learner Volume Breakdown", "RegionalLearnerBreakdown.rdl", "TheStore", sql, "", "", "", "", "Period", DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name"), "LeadRegion", TheLead, "", "", _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub LoadCustomReport()
        'only call me if you are actaully on a custom report

        Dim TheView As DataView
        Dim BrowserSQL As String

        ' ShowLoadingForm()
        Job("Loading report...")

        Dim Params As New List(Of Object)

        Params.Clear()

        Params.Add("Select Report_ID, Report_Name, RTF, SQL, Left_M, Right_M, Top_M, Bottom_M, Alignment from RD_Published_Reports where Report_ID = '" & TheTreeView.SelectedNode.Name & "'")
        Params.Add("Loading report, please wait...")
        If DoSomeWork("LoadBusyDataView", Params, False) = Windows.Forms.DialogResult.OK Then

            TheView = BusyButFine.ReturnedValues(0)

        End If

        If TheView.Count = 0 Then
            MessageBox.Show("Could not find the report", "Report not found", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ' HideLoadingForm()
            Job("Ready")
            Exit Sub
        End If

        Dim addsql As String

        If (ProvidersBox.SelectedValue = 0) Or (ProviderPanel.Visible = False) Then
            If LABox.SelectedValue.ToString = "<All>" Then
                If RegionBox.SelectedValue.ToString = "<All>" Then
                    'every single provider
                    addsql = ""
                Else
                    'specific region
                    addsql = " where BR.Region_code = '" & RegionBox.SelectedValue.ToString & "'"
                End If
            Else
                addsql = " where BR.LA_Code = '" & LABox.SelectedValue.ToString & "'"
            End If
        Else
            If LABox.SelectedValue.ToString = "<All>" Then
                addsql = " where BR.UPIN = " & ProvidersBox.SelectedValue
            Else
                addsql = " where BR.UPIN = " & ProvidersBox.SelectedValue & " and BR.LA_Code = '" & LABox.SelectedValue.ToString & "'"
            End If
        End If


        '  HideLoadingForm()

        With ReportBrowserForm
            .MdiParent = MainForm
            .Dock = DockStyle.Fill
            .Text = TheTreeView.SelectedNode.Text
            .RecordSelector.Items.Clear()
            .PreviousBut.Visible = True
            .NextBut.Visible = True
            .RecordSelector.Visible = True

            BrowserSQL = "select * from (" & TheView(0).Item("sql") & ") BR " & addsql

            .LoadBrowserReport(TheTreeView.SelectedNode.Name, BrowserSQL, "", TheView(0).Item("RTF"), TheView(0).Item("Left_M"), TheView(0).Item("Right_M"), TheView(0).Item("Top_M"), TheView(0).Item("Bottom_M"), TheView(0).Item("Alignment"))
            .Show()
        End With



        Job("Ready")

    End Sub

    Private Sub ChangeLAProvider()
        If LABox.SelectedValue = "<All>" Then Exit Sub

        Dim TheDataView As DataView
        Dim SQL As String

        SQL = "Select * from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "' and Provider_Type_Code = 'Local'"

        TheDataView = LoadDataView(SQL)

        If TheDataView.Count = 0 Then
            ProvidersBox.SelectedValue = 0
        Else
            ProvidersBox.SelectedValue = TheDataView(0).Item("UPIN")
        End If

    End Sub

    Private Sub Appendix1()
        Dim SQL1 As String
        Dim SQL2 As String
        Dim SQL3 As String
        Dim SubSQL As String
        Dim TheDataView As DataView
        Dim StartDate, EndDate As String
        Dim ProvSQL, ProvWhere, ProvVolSQL As String


        If TheTreeView.SelectedNode.Name = "App1LANode" Then
            If LABox.SelectedValue = "<All>" Then
                HideLoadingForm()

                MessageBox.Show("You cannot select all Local Authorities for this report.  Please select a Local Authority.", "Please select a Local Authority", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            'Local Authorities
            ProvSQL = "select UPIN from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "' and (provider_type_code = 'Local' or provider_type_code = 'Schoo')"
            ProvVolSQL = "select UPIN from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "' and provider_type_code = 'Local' "


            'only show a school if it has closing school funding
            ProvWhere = "and (P.provider_type_code = 'Local' or (P.Provider_Type_Code = 'Schoo' and pp.Allocation_ID = 'YPN25')) "
        Else
            If ProvidersBox.SelectedValue = 0 Then
                HideLoadingForm()

                MessageBox.Show("You cannot select all providers for this report.  Please select a provider.", "Please select a provider", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            'Provider
            ProvSQL = ProvidersBox.SelectedValue
            ProvWhere = ""
            ProvVolSQL = ProvidersBox.SelectedValue

        End If

        SQL1 = "select P.LA_Code, P.LA_Name, PP.Period_ID, b.Budget_ID, " & _
                "case " & _
                "  when b.Budget_ID in ('YPLRA', 'YPLRB') then 1 " & _
                "  when b.Budget_ID = 'YPLRC' then 2 " & _
                "  else 3 " & _
                "end as Budget_Group, " & _
                "B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
                "PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners, P.UPIN, P.Provider_Name, P.Region_Name " & _
                "from PaymentProfiles PP " & _
                "left outer join All_Provider_Fields P on (pp.RID = P.RID), " & _
                "     Budgets B " & _
                "where  " & _
                "B.Budget_ID = PP.Budget_ID " & _
                "and P.UPIN in (" & ProvSQL & ") " & _
                "and B.Budget_ID <> 'YPLRC' " & _
                ProvWhere & _
                "and pp.Period_ID = " & PeriodBox.SelectedValue

        SQL1 = SQL1 & " union all " & "select PP.LA_Code, LA.LA_Name, PP.Period_ID, b.Budget_ID, 2 as Budget_Group, B.Budget_Description, PP.Allocation_Description, pp.Allocation_ID, PP.Month, " & _
                "PP.Month_Total as Month_Total, 0 as FYMonth, PP.Learner_Numbers as Learners, 0 as UPIN, '' as Provider_Name, '' as Region_Name " & _
                "from S6F_PaymentProfiles PP, dbo.Local_Authorities LA, Budgets B " & _
                "where  " & _
                "B.Budget_ID = PP.Budget_ID " & _
                "and PP.LA_Code = '" & LABox.SelectedValue & "' " & _
                "and pp.Period_ID = " & PeriodBox.SelectedValue & " " & _
                "and PP.LA_Code = LA.LA_Code " & _
                " " & _
                "union all " & _
                " " & _
                "select PP.LA_Code, LA.LA_Name, Period_ID, 'Other' as Budget_ID, 2 as Budget_Group, 'Other LA Funding' as Budget_Description,  " & _
                "Allocation_Description, Allocation_ID, Month, Month_Total, FYMonth, 0 as Learners, 0 as UPIN, '' as Provider_Name, '' as Region_Name " & _
                "from dbo.SEN_TPG_PaymentProfiles PP, dbo.Local_Authorities LA " & _
                "Where PP.LA_Code = '" & LABox.SelectedValue & "' " & _
                "and Period_ID = " & PeriodBox.SelectedValue & " " & _
                "and PP.LA_Code = LA.LA_Code"

        SQL2 = "SELECT     FLP.Field_Code, FLP.Order_Field, FLP.Field_Description, FLP.Category_Code, FLP.Has_Achievement, FLP.Is_Total_Line, FLP.Is_Header_Line, " & _
                "                      FLP.Is_Auto_Total_Line, Act89.Learners AS '2008/09 F05 Learners', Act910.Learners AS '2009/10 F01 Learners', VD.Learners AS '2010/11 Learners',  " & _
                "                      VD.Achievements AS '2010/11 Achievements', FLP.RID, FLP.La_code, FLP.LA_Name, FLP.UPIN, FLP.Provider_Name " & _
                "FROM         (SELECT     Fields_List_Part.Field_Code, Fields_List_Part.Order_Field, Fields_List_Part.Field_Description, Fields_List_Part.Category_Code,  " & _
                "                                              Fields_List_Part.Has_Achievement, Fields_List_Part.Is_Total_Line, Fields_List_Part.Is_Header_Line, Fields_List_Part.Is_Auto_Total_Line,  " & _
                "                                              All_Provider_Fields.UPIN, All_Provider_Fields.Provider_Name, All_Provider_Fields.Provider_Type_Code, All_Provider_Fields.URN,  " & _
                "                                              All_Provider_Fields.Establishment_Number, All_Provider_Fields.RID, All_Provider_Fields.La_code, All_Provider_Fields.LA_Name,  " & _
                "                                              All_Provider_Fields.Region_Code, All_Provider_Fields.Region_Name " & _
                "                       FROM          Fields_List_Part CROSS JOIN " & _
                "                                              All_Provider_Fields) AS FLP LEFT OUTER JOIN " & _
                "                          (SELECT     Field_Code, Period_ID, Learners, Achievements, RID " & _
                "                            FROM          (SELECT     v.RID, v.Field_Code, v.Period_ID, COALESCE (alloc.Learner_Numbers, v.Learners) AS Learners, v.Achievements " & _
                "                                                    FROM          Volumes_Data AS v LEFT OUTER JOIN " & _
                "                                                                               (SELECT     a.Period_ID, a.RID, a.Learner_Numbers, al.Field_Code " & _
                "                                                                                 FROM          Allocations AS a INNER JOIN " & _
                "                                                                                                        Allocation_Lines AS al ON a.Allocation_ID = al.Allocation_ID " & _
                "                                                                                 WHERE      (al.Field_Code > 0) AND (a.Status = 'Approved') and (al.Budget_ID <> 'YPLRC')) AS alloc ON v.RID = alloc.RID AND  " & _
                "                                                                           v.Field_Code = alloc.Field_Code AND v.Period_ID = alloc.Period_ID) AS Volumes " & _
                "                            WHERE      (Period_ID = " & PeriodBox.SelectedValue & ")) AS VD ON FLP.Field_Code = VD.Field_Code AND FLP.RID = VD.RID LEFT OUTER JOIN " & _
                "                          (SELECT     Provider_Region, Field_Code, Learners, RID " & _
                "                            FROM          YP_200809F05_Act) AS Act89 ON FLP.Field_Code = Act89.Field_Code AND FLP.RID = Act89.RID LEFT OUTER JOIN " & _
                "                          (SELECT     Provider_Region, Field_Code, Learners, RID " & _
                "                            FROM          YP_200910F01_Act) AS Act910 ON FLP.Field_Code = Act910.Field_Code AND FLP.RID = Act910.RID " & _
                "WHERE     (FLP.UPIN in (" & ProvVolSQL & ")) " & _
                "ORDER BY FLP.LA_Name, FLP.Order_Field"

        SQL3 = "Select *, (Total_FY_Funding_S6F + SEN + TPG) as Total_LA_FY_Allocation from " & _
                "(select  LA_Code, LA_Name, sum(Baseline_Funding + Dip_Funding) as Baseline_Funding, " & _
                "       sum(ALS_Funding + Dip_ALS) as ALS_Funding, " & _
                "       sum(Pension_Funding) as Pension_Funding, " & _
                "       sum(Transitional_Prot_Funding) as Transitional_Prot_Funding, " & _
                "       sum(Baseline_Funding + ALS_Funding + Pension_Funding + Transitional_Prot_Funding + Total_Dip_Funding) as TotalS6F " & _
                "from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & " GROUP BY LA_Code, LA_Name) S6F, " & _
                "(select sum(Total_Funding_4Months) as Total_Funding_4Months, " & _
                "       sum(Total_Funding_8Months) as Total_Funding_8Months, " & _
                "       sum(Total_Funding_4Months + Total_Funding_8Months) as Total_FY_Funding_S6F " & _
                "from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") FY, " & _
                "(SELECT sum(SEN) as SEN, " & _
                "       sum(TPG) as TPG " & _
                "FROM SEN_TPG_Funding  where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") ST "

        SubSQL = "Select * from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ""

        TheDataView = LoadDataView("select * from Periods where Period_ID = " & PeriodBox.SelectedValue.ToString)
        StartDate = FormatDateTime(TheDataView(0).Item("Start_Date"), DateFormat.LongDate)
        EndDate = FormatDateTime(TheDataView(0).Item("End_Date"), DateFormat.LongDate)


        ShowReport("Appendix 1", "Contract_Appendixes.rdl", "TheStore", SQL1, "Volumes", SQL2, "LASummary", SQL3, "PeriodString", "Allocation Start Date: " & StartDate & "          Allocation End Date: " & EndDate, _
                   "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "S6F_Summary", SubSQL, PrintIt, True)
    End Sub


    Private Sub LASummaryReport()
        If PeriodBox.SelectedValue = 2010111 Then
            Dim sql As String
            Dim sql2 As String
            Dim sql3 As String

            Dim TheHeader As String


            TheHeader = "Region - " & DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name") & _
                        ", Local Authority - " & DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name")

            sql = "Select *, (Total_FY_Funding_S6F + SEN + TPG) as Total_LA_FY_Allocation from " & _
                    "(select sum(Learner_Funding + Diploma_Funding) as Learner_Funding, " & _
                    "       sum(Total_ALS) as Total_ALS, " & _
                    "       sum(coalesce(LLDD_Programme_Funding,0)) as LLDD_Programme_Funding, " & _
                    "       sum(coalesce(LLDD_ALS,0)) as LLDD_ALS, " & _
                    "       sum(Learner_Funding + Diploma_Funding + Total_ALS + coalesce(LLDD_Programme_Funding,0) + coalesce(LLDD_ALS,0)) as TotalFE " & _
                    "from FE_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") FE, " & _
                    "(select sum(Baseline_Funding + Dip_Funding) as Baseline_Funding, " & _
                    "       sum(ALS_Funding + Dip_ALS) as ALS_Funding, " & _
                    "       sum(Pension_Funding) as Pension_Funding, " & _
                    "       sum(Transitional_Prot_Funding) as Transitional_Prot_Funding, " & _
                    "       sum(Baseline_Funding + ALS_Funding + Pension_Funding + Transitional_Prot_Funding + Total_Dip_Funding) as TotalS6F " & _
                    "from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") S6F, " & _
                    "(select sum(Total_Funding_4Months) as Total_Funding_4Months, " & _
                    "       sum(Total_Funding_8Months) as Total_Funding_8Months, " & _
                    "       sum(Total_Funding_4Months + Total_Funding_8Months) as Total_FY_Funding_S6F " & _
                    "from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") FY, " & _
                    "(SELECT sum(SEN) as SEN, " & _
                    "       sum(TPG) as TPG " & _
                    "FROM SEN_TPG_Funding  where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ") ST "

            sql2 = "Select * from FE_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ""

            sql3 = "Select * from S6F_Summary where LA_Code = '" & LABox.SelectedValue.ToString & "' AND Period_ID = " & PeriodBox.SelectedValue & ""



            ' LoadReportWithSub(ReportLabel.Text, "LA_Summary.rpt", sql, sql2, sql3, "LA", DirectCast(LABox.SelectedItem, DataRowView).Item("LA_Name"), "Region", DirectCast(RegionBox.SelectedItem, DataRowView).Item("Region_Name"), "", "", "", "", "", "")


            ShowReport(ReportLabel.Text, "LASummary.rdl", "TheStore", sql, "", "", "", "", "ReportHeader", TheHeader, "Academic_Year_Text", "  " & DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name"), "Financial_Year_Text", "", "", "", "", "", "", "", "", "", "", "", _
                            "FESummary", sql2, "S6F_Summary", sql3, False, False)
        End If

        If PeriodBox.SelectedValue = 2011121 Then

            Dim TheView As DataView
            Dim sql As String

            'Load the selector..tor tor tor....!
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code order by la.LA_Name "
                Else
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and la.LA_Code = '" & LABox.SelectedValue & "' order by la.LA_Name"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and r.Region_Code = '" & RegionBox.SelectedValue & "' order by la.LA_Name"
                Else
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and la.LA_Code = '" & LABox.SelectedValue & "' order by la.LA_Name"
                End If
            End If

            TheView = LoadDataView(sql)

            With ReportViewerForm
                .TheReportToRun = "LA_Summary1112"
                .PDFButton.Visible = True
                .SelectBox.Visible = True
                .SelectBox.DataSource = TheView
                .SelectBox.ValueMember = "LA_Code"
                .SelectBox.DisplayMember = "LA_Name"
                .RunReport()
            End With

        End If

        If PeriodBox.SelectedValue = 2012131 Then

            Dim TheView As DataView
            Dim sql As String

            'Load the selector..tor tor tor....!
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code order by la.LA_Name "
                Else
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and la.LA_Code = '" & LABox.SelectedValue & "' order by la.LA_Name"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and r.Region_Code = '" & RegionBox.SelectedValue & "' order by la.LA_Name"
                Else
                    sql = "select r.Region_Code, r.Region_Name, la.LA_Code, la.LA_Name from dbo.Regions r, dbo.Local_Authorities la where r.Region_Code = la.Region_Code " & _
                " and la.LA_Code = '" & LABox.SelectedValue & "' order by la.LA_Name"
                End If
            End If

            TheView = LoadDataView(sql)

            With ReportViewerForm
                .TheReportToRun = "LA_Summary1213"
                .PDFButton.Visible = True
                .SelectBox.Visible = True
                .SelectBox.DataSource = TheView
                .SelectBox.ValueMember = "LA_Code"
                .SelectBox.DisplayMember = "LA_Name"
                .RunReport()
            End With

        End If

    End Sub

    'Previous version prior to report name changes to reflect the draft and final annex reports
    'Spooner R 15/03/2012
    'Private Sub ACAD_Load_GAG_AnnexA()
    '    Dim TheView As DataView
    '    Dim sql As String

    '    'Load the selector..tor tor tor....!
    '    If ProvidersBox.SelectedValue = 0 Then
    '        If RegionBox.SelectedValue = "<All>" Then
    '            If LABox.SelectedValue = "<All>" Then
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
    '            Else
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and LA_Code = '" & LABox.SelectedValue & "'"
    '            End If
    '        Else
    '            If LABox.SelectedValue = "<All>" Then
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and Region_Code = '" & RegionBox.SelectedValue & "'"
    '            Else
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and LA_Code = '" & LABox.SelectedValue & "'"
    '            End If
    '        End If
    '    Else
    '        sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and UPIN = " & ProvidersBox.SelectedValue
    '    End If

    '    sql = sql & " order by Provider_Name "

    '    TheView = LoadDataView(sql)

    '    With ReportViewerForm
    '        .TheReportToRun = "rpt_Acad_GAG_Annex_A"
    '        .PDFButton.Visible = True
    '        .SelectBox.Visible = True
    '        .SelectBox.DataSource = TheView
    '        .SelectBox.ValueMember = "RID"
    '        .SelectBox.DisplayMember = "Provider_Name"
    '        .RunReport()
    '    End With
    'End Sub


    'Previous version prior to report name changes to reflect the draft and final annex reports
    'Spooner R 15/03/2012
    'Private Sub ACAD_Load_GAG_AnnexC()
    '    Dim TheView As DataView
    '    Dim sql As String

    '    'Load the selector..tor tor tor....!
    '    If ProvidersBox.SelectedValue = 0 Then
    '        If RegionBox.SelectedValue = "<All>" Then
    '            If LABox.SelectedValue = "<All>" Then
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
    '            Else
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and LA_Code = '" & LABox.SelectedValue & "'"
    '            End If
    '        Else
    '            If LABox.SelectedValue = "<All>" Then
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and Region_Code = '" & RegionBox.SelectedValue & "'"
    '            Else
    '                sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and LA_Code = '" & LABox.SelectedValue & "'"
    '            End If
    '        End If
    '    Else
    '        sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
    '            " and UPIN = " & ProvidersBox.SelectedValue
    '    End If

    '    sql = sql & " order by Provider_Name "

    '    TheView = LoadDataView(sql)

    '    With ReportViewerForm
    '        .TheReportToRun = "rpt_Acad_GAG_Annex_C"
    '        .PDFButton.Visible = True
    '        .SelectBox.Visible = True
    '        .SelectBox.DataSource = TheView
    '        .SelectBox.ValueMember = "RID"
    '        .SelectBox.DisplayMember = "Provider_Name"
    '        .RunReport()
    '    End With
    'End Sub

    Private Sub ACAD_Load_GAG_AnnexADraft()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_GAG_Annex_A_DRAFT_FINAL"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub

    Private Sub ACAD_Load_GAG_AnnexAFinal()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_GAG_Annex_A_FINAL_FINAL"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub
    Private Sub ACAD_Load_GAG_AnnexCDraft()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_GAG_Annex_C_DRAFT_FINAL"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub
    Private Sub ACAD_Load_GAG_AnnexCFinal()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_GAG_Annex_C_FINAL_FINAL"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub

    Private Sub ACAD_Load_GAG_Schedule()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector..tor tor tor....!
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_GAG_SCHEDULE"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub

    Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        With Search_Form
            .WhoSentForMe = "Reports"
            If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                'find the provider in the list
                With .SearchResults_DataViewGrid
                    RegionBox.SelectedValue = .Item(.Columns("Region_Code").Index, .CurrentRow.Index).Value.ToString
                    LABox.SelectedValue = "<All>"
                    'LABox.SelectedValue = .Item(.Columns("LA_Code").Index, .CurrentRow.Index).Value.ToString
                    FilterBox.SelectedValue = "All"
                    ProvidersBox.SelectedValue = .Item(.Columns("UPIN").Index, .CurrentRow.Index).Value.ToString

                End With
            End If
        End With


        ' If ProvidersBox.SelectedIndex = -1 Then
        ' ProvidersBox.SelectedIndex = 0
        ' MessageBox.Show("The provider you selected could not be found with the current selection criteria.  Try changing the Local Authority or Region to <All>.", "Cant find provider", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        '        End If
    End Sub


    Private Sub PrintBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrintBut.Click

        ShowLoadingForm()

        PrintIt = True

        Appendix1()

        Appendix1ai()

        Appendix1aii()

        Appendix1bi()

        Appendix1bii()

        Appendix1c()

        ReportViewerForm.Close()
        HideLoadingForm()

    End Sub



    Private Sub FilterBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FilterBox.SelectedIndexChanged
        LoadProvidersBox()
    End Sub

    Private Sub Bursary1618Report()

        Dim PeriodText As String
        Dim sql As String

        PeriodText = DirectCast(PeriodBox.SelectedItem, DataRowView).Item("Period_Name")

        sql = "Select a.Period_ID, p.Region_Name, p.LA_Name, p.UAI, p.UPIN, " & _
            "  p.Provider_Name, p.Provider_Type_Description, a.Allocation, a.Learner_Numbers " & _
            "	from dbo.Allocations as A " & _
            "	Left outer join dbo.All_Provider_Fields as P on P.RID=A.RID " & _
            "Where Allocation_ID='YPG01' and [Status]='Approved'  " & _
            "and period_id = " & PeriodBox.SelectedValue & " "

        'ReportType = "Provider"

        If ProvidersBox.SelectedValue = 0 Then
            'All providers
            If LABox.SelectedValue = "<All>" Then
                'All LAs
                If RegionBox.SelectedValue = "<All>" Then
                    'All Regions
                Else
                    sql = sql & "and P.UPIN in (Select distinct UPIN from All_Provider_Fields where Region_Code = '" & RegionBox.SelectedValue & "')"
                End If
            Else
                sql = sql & "and P.UPIN in (Select distinct UPIN from All_Provider_Fields where LA_Code = '" & LABox.SelectedValue & "')"
            End If

        End If

        ShowReport("16-19 Bursary Allocations", "rpt_1619_Bursary_Allocations.rdl", "TheStore", sql, "", "", "", "", _
                   "Period", PeriodText, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", False, False)

        'ShowReport("CCM Payment Schedule", "CCM_Funding_Agreement.rdl", "TheStore", sql, "", "", "", "",
        ' "ReportType", ReportType, "ProviderHeader", ProviderText, "Period", PeriodText, "", "", "", "", "", "", _
        '  "", "", "", "", "", "", "", "", False, False)

    End Sub

    Private Sub ACAD_Load_Conv_GAG_Indic()
        Dim TheView As DataView
        Dim sql As String

        'Load the selector
        If ProvidersBox.SelectedValue = 0 Then
            If RegionBox.SelectedValue = "<All>" Then
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 "
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            Else
                If LABox.SelectedValue = "<All>" Then
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and Region_Code = '" & RegionBox.SelectedValue & "'"
                Else
                    sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and LA_Code = '" & LABox.SelectedValue & "'"
                End If
            End If
        Else
            sql = "select RID, Provider_Name from all_provider_fields where Provider_Type_Code = 'Acade' and Lead = 1 " & _
                " and UPIN = " & ProvidersBox.SelectedValue
        End If

        sql = sql & " order by Provider_Name "

        TheView = LoadDataView(sql)

        With ReportViewerForm
            .TheReportToRun = "rpt_Acad_Converters_GAG_Annex_A"
            .PDFButton.Visible = True
            .SelectBox.Visible = True
            .SelectBox.DataSource = TheView
            .SelectBox.ValueMember = "RID"
            .SelectBox.DisplayMember = "Provider_Name"
            .RunReport()
        End With
    End Sub

End Class